/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bean;

import Objects.User;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import javax.ejb.Stateless;

/**
 *
 * @author ntfsbk
 */
@Stateless(mappedName = "userBean")
public class userBean implements userBeanRemote {

    String connectionString = "jdbc:sqlserver://NTFSBK-PC\\SQLEXPRESS_R2;"
            + "databaseName=imageSite";
    String serverUserName = "sa";
    String serverPasswd = "123456";

    @Override
    public boolean checkLogin(String userName, String Passwd) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE userName=? AND password=?");
            pstm.setString(1, userName);
            pstm.setString(2, Passwd);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            throw e;
        }
    }

    private int createProfileAlbum(int userId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("insert into Albums(albumName,userId,dateCreate)values('profileAlbum',?,?)");

            Date calendar = new Date();
            int year = calendar.getYear() + 1990;
            int month = calendar.getMonth();
            int date = calendar.getDate();
            String createdDate = year + "-" + month + "-" + date;

            pstm.setInt(1, userId);
            pstm.setString(2, createdDate);
            pstm.executeUpdate();

            pstm = conn.prepareStatement("select max(albumId) as albumId from Albums");

            ResultSet rs = pstm.executeQuery();
            rs.next();
            String albumIdString = rs.getString("albumId");
            if (albumIdString == null) {
                return -1;
            }
            int albumId = Integer.parseInt(albumIdString);

            return albumId;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public boolean checkExistedUser(String userName) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE userName=?");
            pstm.setString(1, userName);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public boolean checkIsAssmin(String userName) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE userName=? AND isAssmin=1");
            pstm.setString(1, userName);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            //throw e;
        }
        return false;
    }

    @Override
    public String checkIsBlock(String userName) {
        String expirationDateStr;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT expirationDate FROM Users WHERE userName=?");
            pstm.setString(1, userName);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                if (rs.getDate("expirationDate") != null) {
                    expirationDateStr = rs.getDate("expirationDate").toString();
                    if (expirationDateStr.compareTo("2025-01-01") == 0) {
                        expirationDateStr = "forever";
                    }
                    return expirationDateStr;
                } else {
                    expirationDateStr = "ActiveUser";
                }
            }
        } catch (Exception e) {
            //throw e;
        }
        return "ActiveUser";
    }

    @Override
    public boolean insertNewUser(String userName, String passwd, String email, String gender, String birth) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("INSERT INTO Users(userName, password, email,gender,birth) VALUES (?,?,?,?,?)");
            pstm.setString(1, userName);
            pstm.setString(2, passwd);
            pstm.setString(3, email);
            pstm.setString(4, gender);
            pstm.setString(5, birth);
            pstm.executeUpdate();

            pstm = conn.prepareStatement("select max(userId) as userId from Users");
            ResultSet rs = pstm.executeQuery();
            rs.next();
            int usetrId = Integer.parseInt(rs.getString("userId"));
            int profileId = createProfileAlbum(usetrId);

            pstm = conn.prepareStatement("update Users set profileAlbumId=? where userId=?");
            pstm.setInt(1, profileId);
            pstm.setInt(2, usetrId);
            pstm.executeUpdate();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public boolean checkExistedEmail(String email) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE email=?");
            pstm.setString(1, email);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public int getUserIdByUserName(String userName) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE userName=?");
            pstm.setString(1, userName);
            ResultSet rs = pstm.executeQuery();

            if (rs.next()) {
                return rs.getInt("userId");
            } else {
                return -1;
            }
        } catch (Exception e) {
            //throw e;
        }
        return -2;
    }

    public String getFileNameFromLink(String link) {

        String suffix = "";
        int pos = link.lastIndexOf('/');
        if (pos > 0 && pos < link.length() - 1) {
            suffix = link.substring(pos + 1);
        }
        return suffix;
    }

    @Override
    public String getFileNameThumbnailOfAlbums(int albumId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Albums WHERE albumId=?");
            pstm.setInt(1, albumId);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                return null;
            }

            String link = rs.getString("thumbnail");
            pstm.close();
            if (link == null) {
                return null;
            }

            return getFileNameFromLink(link);
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public User getUserInfoByUserId(int userId) {
        User u;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT * FROM Users WHERE userId=?");
            pstm.setString(1, Integer.toString(userId));
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                u = new User(rs.getInt("userId"), rs.getString("userName"));
                u.setEmail(rs.getString("email"));
                u.setGender(rs.getString("gender"));
                u.setProfileAlbumId(rs.getInt("profileAlbumId"));
                u.setBirth(rs.getString("birth"));
                u.setPassword(rs.getString("password"));
                return u;
            }
            pstm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new User();
    }

    @Override
    public boolean updateGender(String gender, int userId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Users SET gender=? WHERE userId=?");
            pstm.setString(1, gender);
            pstm.setInt(2, userId);
            pstm.executeUpdate();

            pstm.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updateBirth(String birth, int userId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Users SET birth=? WHERE userId=?");
            pstm.setString(1, birth);
            pstm.setInt(2, userId);
            pstm.executeUpdate();

            pstm.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updatePass(String pass, int userId) throws Exception {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("UPDATE Users SET password=? WHERE userId=?");
            pstm.setString(1, pass);
            pstm.setInt(2, userId);
            pstm.executeUpdate();

            pstm.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public int getNumberOfAlbums(int userId) throws Exception {
        int number=1;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT count(*)as number FROM Albums,Users WHERE Albums.userId=Users.userId AND Users.userId=?");
            pstm.setInt(1, userId);
            ResultSet rs=pstm.executeQuery();

            rs.next();
            number=Integer.parseInt(rs.getString("number"));          
            pstm.close();
            return number;
        } catch (Exception e) {
            e.printStackTrace();
            return number;
        }
    }

    @Override
    public int getNumberOfImages(int userId) throws Exception {
        int number = 1;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection(connectionString, serverUserName, serverPasswd);

            PreparedStatement pstm = conn.prepareStatement("SELECT count(*)as number FROM Albums,Users,Images WHERE Albums.userId=Users.userId AND Images.albumId=Albums.albumId AND Users.userId=?");
            pstm.setInt(1, userId);
            ResultSet rs = pstm.executeQuery();

            rs.next();
            number = Integer.parseInt(rs.getString("number"));
            pstm.close();
            return number;
        } catch (Exception e) {
            e.printStackTrace();
            return number;
        }
    }
}
